\documentclass[a4paper,10pt]{article}
\usepackage[utf8x]{inputenc}
\usepackage[spanish]{babel}
\usepackage{textcomp}
\usepackage{listings}

%opening
\title{ 
      Trabajo Pr\'actico\\
      Recepci\'on del Paciente
}

\author{
           \normalsize{2do. Cuatrimestre de 2011} \\
           \normalsize{75.15 Base de Datos}  \\
           \normalsize{Facultad de Ingenier\'ia, Universidad de Buenos Aires}
}

\date{ Fecha de entrega: 30 de Noviembre del 2011 }

\begin{document}


\maketitle
\thispagestyle{empty}   % quita el n\'umero en la primer p\'agina


%\hspace{5cm}
\begin{center}
 
\begin{tabular}{ | c | c | c | }
\hline
 Apellido y Nombre & Padr\'on & mail \\ \hline
Ramonda, Juan Pablo & 87991 & juanpr@gmail.com \\ \hline
Hurtado, Pablo & 89542 & hurtado.pani@gmail.com \\ \hline
Di Paola, Mart\'in & 88961 & martinp.dipaola@gmail.com \\ \hline
Villanueva, Amalia M. & 89380 & amaliavillanueva@gmail.com \\ \hline
\end{tabular}
\end{center}

\newpage
%\tableofcontents
%\newpage

\section{Consigna del Trabajo Práctico}

Se deben resolver las consultas incluídas en el documento que se descarga en el link DOCUMENTO utilizando los datos de la planilla excel del link DATOS.

El trabajo se debe resolver utilizando únicamente las sentencias y cláusulas especificadas en la cartilla de sintaxis SQL incluída en el apunte. La presentación debe incluír tanto las sentencias SQL como el resultado de la consulta.


\section{Resolución}

Las relaciones son:

\begin{itemize}
	\item \textbf{PACIENTE }(\underline{nro\_paciente}, cod\_doc, nro\_doc, nombre, apellido, sexo, direccion, telefono, fecha\_nac, nombre\_plan, nombre\_entidad, nro\_afiliado )
	\item \textbf{TURNO} (\underline{nro\_turno}, hora, fecha, sobreturno, nro\_paciente, cod\_lugar, cod\_profesional)
	\item \textbf{PRESTACION} (\underline{cod\_prestacion}, descripción, importe)
	\item \textbf{TURNO\_PRESTACION} (\underline{cod\_prestacion, nro\_turno})
	\item \textbf{PLAN} (\underline{nombre\_plan, nombre\_entidad}, descripción)
	\item \textbf{PLAN\_PRESTACION} (\underline{nombre\_plan, nombre\_entidad, cod\_prestacion}, porcentaje\_cobertura)
	\item \textbf{PROFESIONAL} (\underline{cod\_profesional}, nombre, apellido, domicilio, cod\_especialidad)
	\item \textbf{LUGAR} (\underline{cod\_lugar}, descripción)
\end{itemize}


\begin{enumerate}
	\item El nombre y apellido de los pacientes que tienen o han tenido más de un turno con el mismo profesional.

		\begin{center}
		\begin{tabular}{|l|l|}
		\hline
		nombre & apellido \\
		\hline
		Irma & Rodriguez\\
		Olga & Amour\\
		\hline
		\end{tabular}
		\end{center}


	 \begin{lstlisting}[frame=shadowbox, language=SQL, breaklines=true, basicstyle=\footnotesize]
SELECT p.nombre,p.apellido
FROM Paciente p
WHERE EXISTS(SELECT t.cod_profesional
			FROM Turno t
			WHERE t.nro_paciente = p.nro_paciente
			GROUP BY t.cod_profesional
			HAVING COUNT(*) > 1);		
	 \end{lstlisting}

	\item El/los planes que cubren mayor cantidad de prestaciones.

		\begin{center}
		\begin{tabular}{|l|l|}
		\hline
		nombre\_plan & nombre\_entidad \\
		\hline
		1 & DOSUBA \\
		310 & OSDE \\
		\hline
		\end{tabular}
		\end{center}

	 \begin{lstlisting}[frame=shadowbox, language=SQL, breaklines=true, basicstyle=\footnotesize]
SELECT pp.nombre_plan, pp.nombre_entidad
FROM Plan_Prestacion pp
WHERE pp.porcentaje_cobertura > 0
GROUP BY pp.nombre_plan,pp.nombre_entidad
HAVING COUNT(*) >= ALL (SELECT COUNT(p1.cod_prestacion)
			FROM Plan_Prestacion p1
			WHERE p1.porcentaje_cobertura > 0
			GROUP BY p1.nombre_plan,p1.nombre_entidad);
	 \end{lstlisting}

	\item El/los nombres y apellidos de los profesionales que tienen mayor cantidad de turnos asignados.

		\begin{center}
		\begin{tabular}{|l|l|}
		\hline
		nombre & apellido \\
		\hline
		Alex & Velez\\
		\hline
		\end{tabular}
		\end{center}

	 \begin{lstlisting}[frame=shadowbox, language=SQL, breaklines=true, basicstyle=\footnotesize]
SELECT pr.nombre,pr.apellido
FROM Profesional pr,Turno t
WHERE pr.cod_profesional = t.cod_profesional
GROUP BY pr.cod_profesional,pr.nombre,pr.apellido
HAVING COUNT(*) >= ALL (SELECT COUNT(*)
				FROM Turno
				GROUP BY cod_profesional);
	 \end{lstlisting}

	\item El/las entidades que tienen mayor cantidad de pacientes con turnos reservados.

		\begin{center}
		\begin{tabular}{|l|}
		\hline
		nombre\_entidad \\
		\hline
		OSDE\\
		\hline
		\end{tabular}
		\end{center}

	 \begin{lstlisting}[frame=shadowbox, language=SQL, breaklines=true, basicstyle=\footnotesize]
SELECT p.nombre_entidad
FROM Paciente p,Turno t
WHERE p.nro_paciente = t.nro_paciente
GROUP BY p.nombre_entidad
HAVING COUNT(DISTINCT t.nro_paciente) >= ALL (SELECT COUNT(DISTINCT t.nro_paciente)
				FROM Paciente p,Turno t
				WHERE p.nro_paciente = t.nro_paciente
				GROUP BY p.nombre_entidad);
	 \end{lstlisting}

	\item Por cada entidad el monto a facturar por las prestaciones debido a las prestaciones efectuadas y a efectuar en los turnos.

		\begin{center}
		\begin{tabular}{|l|r|}
		\hline
		nombre\_entidad & importe \\
		\hline
		DOSUBA & 350.0000 \\
		OSDE & 800.0000 \\
		Privado & 0.0000 \\
		\hline
		\end{tabular}
		\end{center}


	 \begin{lstlisting}[frame=shadowbox, language=SQL, breaklines=true, basicstyle=\footnotesize]
SELECT pl.nombre_entidad,SUM(pr.importe*pp.porcentaje_cobertura/100) AS importe
FROM Plan pl,Turno t, Paciente pa, Turno_Prestacion tp, Prestacion pr,Plan_Prestacion pp
WHERE t.nro_paciente = pa.nro_paciente
AND pl.nombre_entidad = pa.nombre_entidad
AND pl.nombre_plan = pa.nombre_plan
AND t.nro_turno = tp.nro_turno
AND tp.cod_prestacion = pr.cod_prestacion
AND pp.nombre_plan = pl.nombre_plan
AND pp.nombre_entidad = pl.nombre_entidad
AND pp.cod_prestacion = tp.cod_prestacion
GROUP BY pl.nombre_entidad
	 \end{lstlisting}

	\item El monto total a pagar por los pacientes debido a las prestaciones efectuadas y a efectuar en los turnos.

		\begin{center}
		\begin{tabular}{|r|l|l|r|}
		\hline
		nro\_paciente & nombre & apellido & importe \\
		\hline
		333 & Carlos & Pedrazzi  &   50.0000 \\
		444 & Irma   & Rodriguez & 1000.0000 \\
		111 & Juan   & Perez     &  150.0000 \\
		222 & Maria  & Gonzales  &    0.0000 \\
		555 & Olga   & Amour     &  150.0000 \\
		\hline
		\end{tabular}
		\end{center}

	 \begin{lstlisting}[frame=shadowbox, language=SQL, breaklines=true, basicstyle=\footnotesize]
SELECT p.nro_paciente,p.nombre,p.apellido,SUM(pr.importe - pr.importe*pp.porcentaje_cobertura/100) AS importe
FROM Paciente p,Turno t,Prestacion pr,Turno_Prestacion tp,Plan_Prestacion pp
WHERE p.nro_paciente = t.nro_paciente
AND t.nro_turno = tp.nro_turno
AND pp.nombre_plan = p.nombre_plan
AND pp.nombre_entidad = p.nombre_entidad
AND pp.cod_prestacion = tp.cod_prestacion
AND pr.cod_prestacion = tp.cod_prestacion
GROUP BY p.nro_paciente,p.nombre,p.apellido
	 \end{lstlisting}

	\item Listado de planes que cubren en algún porcentual todas las prestaciones.

		\begin{center}
		\begin{tabular}{|l|l|}
		\hline
		nombre\_plan & nombre\_entidad \\
		\hline
		1           & DOSUBA         \\
		310         & OSDE  \\
		\hline
		\end{tabular}
		\end{center}

	 \begin{lstlisting}[frame=shadowbox, language=SQL, breaklines=true, basicstyle=\footnotesize]
SELECT pl.nombre_plan,pl.nombre_entidad
FROM Plan pl
WHERE NOT EXISTS(SELECT pr.cod_prestacion
	FROM Prestacion pr
	WHERE NOT EXISTS(SELECT *
		FROM Plan_Prestacion pp
		WHERE pl.nombre_plan = pp.nombre_plan
			AND pl.nombre_entidad = pp.nombre_entidad
			AND pp.cod_prestacion = pr.cod_prestacion
			AND pp.porcentaje_cobertura > 0));
	 \end{lstlisting}

	\item Listado de los profesionales que entre todos los pacientes que atienden se encuentran todos los planes.
		\begin{center}
		 Empty set
		\end{center}
	 \begin{lstlisting}[frame=shadowbox, language=SQL, breaklines=true, basicstyle=\footnotesize]
SELECT pro.cod_profesional,pro.nombre,pro.apellido
FROM Profesional pro
WHERE NOT EXISTS(SELECT *
	FROM Plan pl
	WHERE NOT EXISTS(SELECT *
		FROM Turno tur,Paciente pa
		WHERE pro.cod_profesional = tur.cod_profesional
			AND tur.nro_paciente = pa.nro_paciente
			AND pl.nombre_plan = pa.nombre_plan
			AND pl.nombre_entidad = pa.nombre_entidad));
	 \end{lstlisting}

	\item El listado de los planes cuyo costo económico de cobertura (porcentual del importe) está por debajo del promedio general.

		\begin{center}
		\begin{tabular}{|l|l|}
		\hline
		nombre\_plan & nombre\_entidad \\
		\hline
		210         & OSDE         \\
		Privado	    & Privado      \\
		\hline
		\end{tabular}
		\end{center}

	 \begin{lstlisting}[frame=shadowbox, language=SQL, breaklines=true, basicstyle=\footnotesize]
SELECT pl.nombre_plan,pl.nombre_entidad
FROM Plan pl,Plan_Prestacion pp
WHERE pl.nombre_plan = pp.nombre_plan
AND pl.nombre_entidad = pp.nombre_entidad
GROUP BY pl.nombre_plan,pl.nombre_entidad
HAVING AVG(pp.porcentaje_cobertura) < (SELECT AVG(pp2.porcentaje_cobertura)
					FROM Plan_Prestacion pp2);
	 \end{lstlisting}

	\item Listado de los profesionales con el monto de las prestaciones que realizan desagregado por sexo de los pacientes y entidades.

		\begin{center}
		\begin{tabular}{|l|l|l|r|l|l|}
		 \hline
		  cod\_profesional & nombre  & apellido & importe & sexo & nombre\_entidad \\
		 \hline
		  M100            & Rogelio & Diaz     &     100 & F    & OSDE           \\
		  M100            & Rogelio & Diaz     &     400 & M    & DOSUBA         \\
		  M100            & Rogelio & Diaz     &     200 & M    & OSDE           \\
		  M200            & Edgar   & Agar     &     800 & F    & OSDE           \\
		  M200            & Edgar   & Agar     &     100 & F    & Privado        \\
		  M300            & Alex    & Velez    &     900 & F    & Privado        \\
		 \hline
		\end{tabular}
		\end{center}

	 \begin{lstlisting}[frame=shadowbox, language=SQL, breaklines=true, basicstyle=\footnotesize]
SELECT pro.cod_profesional,pro.nombre,pro.apellido,SUM(pr.importe) AS importe,pa.sexo,pa.nombre_entidad
FROM Profesional pro,Turno t,Prestacion pr,Turno_Prestacion tp,Paciente pa
WHERE pro.cod_profesional = t.cod_profesional
AND t.nro_paciente = pa.nro_paciente
AND t.nro_turno = tp.nro_turno
AND tp.cod_prestacion = pr.cod_prestacion
GROUP BY pro.cod_profesional,pro.nombre,pro.apellido,pa.sexo,pa.nombre_entidad;
	 \end{lstlisting}

\end{enumerate}

\end{document}
